package yunjiao.javatutorials.guava.concurrent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * TODO
 *
 * @author yangyunjiao
 */
public class DatabaseConnectionServiceExample {
    public static void main(String[] args) {
        // 数据库连接配置
        String url = "jdbc:h2:mem:testdb"; // 使用 H2 内存数据库作为示例
        String username = "sa";
        String password = "";
        int poolSize = 5;

        // 创建数据库连接服务
        DatabaseConnectionService dbService = new DatabaseConnectionService(url, username, password, poolSize);

        try {
            // 启动服务
            System.out.println("Starting database connection service...");
            dbService.startAsync().awaitRunning();
            System.out.println("Service started successfully");

            // 演示数据库操作
            demonstrateDatabaseOperations(dbService);

            // 模拟多个线程同时使用连接池
            demonstrateConcurrentUsage(dbService);

        } catch (Exception e) {
            System.err.println("Error occurred: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 停止服务
            System.out.println("Stopping database connection service...");
            dbService.stopAsync().awaitTerminated();
            System.out.println("Service stopped");
        }
    }

    private static void demonstrateDatabaseOperations(DatabaseConnectionService dbService) {
        Connection conn = null;
        try {
            // 获取连接
            System.out.println("Available connections before get: " + dbService.getAvailableConnections());
            conn = dbService.getConnection();
            System.out.println("Available connections after get: " + dbService.getAvailableConnections());

            // 创建测试表
            createTestTable(conn);

            // 插入数据
            insertTestData(conn);

            // 查询数据
            queryTestData(conn);

            // 更新数据
            updateTestData(conn);

            // 再次查询验证更新
            queryTestData(conn);

        } catch (Exception e) {
            System.err.println("Database operation failed: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 释放连接
            if (conn != null) {
                dbService.releaseConnection(conn);
                System.out.println("Available connections after release: " + dbService.getAvailableConnections());
            }
        }
    }

    private static void demonstrateConcurrentUsage(DatabaseConnectionService dbService) {
        System.out.println("\n=== Demonstrating concurrent usage ===");

        // 创建多个线程模拟并发访问
        Thread[] threads = new Thread[3];
        for (int i = 0; i < threads.length; i++) {
            final int threadId = i;
            threads[i] = new Thread(() -> {
                Connection conn = null;
                try {
                    System.out.println("Thread " + threadId + " waiting for connection...");
                    conn = dbService.getConnection();
                    System.out.println("Thread " + threadId + " got connection");

                    // 模拟数据库操作
                    Thread.sleep(1000);

                    // 执行查询
                    PreparedStatement stmt = conn.prepareStatement("SELECT COUNT(*) FROM users");
                    ResultSet rs = stmt.executeQuery();
                    if (rs.next()) {
                        System.out.println("Thread " + threadId + " - Total users: " + rs.getInt(1));
                    }

                    rs.close();
                    stmt.close();

                } catch (Exception e) {
                    System.err.println("Thread " + threadId + " error: " + e.getMessage());
                } finally {
                    if (conn != null) {
                        dbService.releaseConnection(conn);
                        System.out.println("Thread " + threadId + " released connection");
                    }
                }
            });
        }

        // 启动所有线程
        for (Thread thread : threads) {
            thread.start();
        }

        // 等待所有线程完成
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
        }
    }

    private static void createTestTable(Connection conn) throws SQLException {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS users (" +
                "id INT PRIMARY KEY AUTO_INCREMENT, " +
                "name VARCHAR(100), " +
                "email VARCHAR(100), " +
                "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                ")";

        try (PreparedStatement stmt = conn.prepareStatement(createTableSQL)) {
            stmt.execute();
            System.out.println("Test table created successfully");
        }
    }

    private static void insertTestData(Connection conn) throws SQLException {
        String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";

        try (PreparedStatement stmt = conn.prepareStatement(insertSQL)) {
            // 插入多条测试数据
            String[][] testData = {
                    {"Alice", "alice@example.com"},
                    {"Bob", "bob@example.com"},
                    {"Charlie", "charlie@example.com"}
            };

            for (String[] data : testData) {
                stmt.setString(1, data[0]);
                stmt.setString(2, data[1]);
                stmt.addBatch();
            }

            int[] results = stmt.executeBatch();
            System.out.println("Inserted " + results.length + " test records");
        }
    }

    private static void queryTestData(Connection conn) throws SQLException {
        String querySQL = "SELECT id, name, email FROM users ORDER BY id";

        try (PreparedStatement stmt = conn.prepareStatement(querySQL);
             ResultSet rs = stmt.executeQuery()) {

            System.out.println("Current users in database:");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                System.out.printf("  ID: %d, Name: %s, Email: %s%n", id, name, email);
            }
        }
    }

    private static void updateTestData(Connection conn) throws SQLException {
        String updateSQL = "UPDATE users SET email = ? WHERE name = ?";

        try (PreparedStatement stmt = conn.prepareStatement(updateSQL)) {
            stmt.setString(1, "alice.new@example.com");
            stmt.setString(2, "Alice");

            int rowsUpdated = stmt.executeUpdate();
            System.out.println("Updated " + rowsUpdated + " record(s)");
        }
    }
}
